package com.servlet;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@WebServlet(name = "JdbcServlet", value = "/JdbcServlet")
public class JdbcServlet extends HttpServlet {
    /**
     * 数据库访问
     * **/
    //static final String url = "jdbc:sqlserver://localhost:1433;databaseName=testsys;encrypt=false "; // 数据的的访问方式
    static final String url = "jdbc:mysql://1.12.241.180:3305/testsys?useUnicode=true&characterEncoding=utf-8";

    //static final String username = "sa";//数据库名称

    static final String username = "root";//数据库名称
    static final String password = "123456";//访问数据库密码
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// 加载数据库驱动，注册到驱动管理器
            Class.forName("com.mysql.jdbc.Driver");
             conn = DriverManager.getConnection(url,username,password);// 创建Connection连接

             stmt = conn.createStatement();// 获取Statement(获得发送sql的对象)

            String sql = "select * from com_dess";

             rs = stmt.executeQuery(sql);	// 执行查询

            Map map= new HashMap();
            List<Map> list = new ArrayList<>();
            //执行结果，打印到控制台
            while (rs.next()){
                int id = rs.getInt("id");

                String username = rs.getString("name");

                System.out.println(id + "===" + username + ":::" );

                //响应到JSP页面
                map.put("id",id);
                map.put("name",username);
                list.add(map);
            }

            request.setAttribute("list", list); //集合放置到request中
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try { rs.close();	 } catch (SQLException e) {	throw new RuntimeException(e);	} // 关闭ResultSet

            try {stmt.close();} catch (SQLException e) {throw new RuntimeException(e);} // 关闭Statement

            try {conn.close();} catch (SQLException e) {throw new RuntimeException(e);}// 关闭Connection

        }

        // 请求转发到
        request.getRequestDispatcher("JSP/List.jsp").forward(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}
